<?php
require_once('MySqlConnection.php');
require_once('User.php');

class UserDAL{
	private $_connection;
	//Singleton pattern
    private static $_instance = null;
	public static function Instance(){
		if (self::$_instance == null) self::$_instance = new UserDAL();
		return self::$_instance;
	}
	private function __construct(){
		include_once('./include/db.inc');
		$this->_connection = MySqlConnection::Instance($server,$user,$password,$database);
	}
	
	public function getUsers(){
		$this->_connection->Open();
		$sqlString = "select u.*, s.sex from user u JOIN salutation s ON u.salutation_id = s.id";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
	
	public function getUserByName($name){
		$this->_connection->Open();
		$sqlString = "select * from user where CONCAT_WS(' ',first_name, last_name) LIKE '%{$name}%'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
	
	public function getUserByUsername($username){
		$this->_connection->Open();
		$sqlString = "select * from user where username = '{$username}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
	
	
	public function loginUser($user){
		$this->_connection->Open();
		$username = $user['username'];
		$hashedPass = md5($user['password']);
		$sqlString = "SELECT u.*, (SELECT COUNT(*) FROM admin a WHERE a.user_id = u.id) as admin FROM user u WHERE u.username = '{$username}' AND u.password = '{$hashedPass}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
	
/** |Onnodig geworden denk ik|
	public function getDoctorsByDistrict($district_id){
		$this->_connection->Open();
		$sqlString = "select * from user u join location l on u.location_id = l.id where district_id = '{$district_id}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
**/
	
	public function getUserById($userId){
		$this->_connection->Open();
		$sqlString = "select * from user where id = '{$userId}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'user');
	}
	
/** |Onnodig geworden|	
	public function getDoctorsByPostalNumberOrLocationName($searchString){
		$this->_connection->Open();
		$sqlString = "select * from doctors d join location l on d.location_id = l.id where (l.postal_number = '{$searchString}') OR l.city_name LIKE '%{$searchString}%'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'doctor');
	}
**/
	
	public function addUser($userToAdd){
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("INSERT INTO user(first_name,last_name,username,password,salutation_id,phone,email,web,registration,activated,avatar,invoice_address_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)")) {
			$hashPass = md5($userToAdd['password']);
			$stmt->bind_param("ssssissssisi", $userToAdd['first_name'], $userToAdd['last_name'], $userToAdd['username'], $hashPass, $userToAdd['salutation_id'], $userToAdd['phone'], $userToAdd['email'], $userToAdd['web'], date("Y-m-d"), $userToAdd['activated'], $userToAdd['avatar'], $userToAdd['invoice_address_id']);
			$success = $stmt->execute();
			if(!$success){
				echo "Inserting user failed!";
			}
			else{
				$resultArray[] = $mysqli->insert_id;
				return $resultArray; 
			}
		}
		else{
			
			echo "Failed preparing insert user statement!";
		}
		$mysqli -> close();
	}
	
	public function removeUser($userId){
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("DELETE FROM user WHERE id = '?'")) {
			$stmt->bind_param("i", $userId);
			$success = $stmt->execute();
			if(!$success){
				echo "Removing user failed!";
			}
		}
		else{
			
			echo "Failed preparing delete user statement!";
		}
		$mysqli -> close();
	}
	
	public function updateUser($us) {
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
		if($stmt = $mysqli->prepare("UPDATE user SET first_name = ?, last_name = ?, username = ?, password = ?, salutation_id = ?, phone = ?, email = ?, web = ?, registration = ?, activated = ?, avatar = ?, invoice_address_id = ? WHERE id = ?")) {
			$stmt->bind_param("ssssissssisii", $us['first_name'], $us['last_name'], $us['username'], $us['password'], $us['salutation_id'], $us['phone'], $us['email'], $us['web'], $us['registration'], $us['activated'], $us['avatar'], $us['invoice_address_id'], $us['id']);
			$success = $stmt->execute();
			if(!$success){
				echo "Updating user failed!";
			}
		}
		else{
			
			echo "Failed preparing update user statement!";
		}
		$mysqli -> close();
	}
	
	//MAPPPING
	
	private function Mapping($queryData,$resultType){
		foreach($queryData as $data){
			$resultArray[] = $this->Map($data,$resultType);
		}
		return $resultArray;
	}
	
	private function Map($data,$resultType){
		$act = ($data->activated > 0);
		$data->admin = ($data->admin == 1);
		$object = new User($data->id,$data->first_name,$data->last_name,$data->username,$data->password,$data->salutation_id,$data->phone,$data->email,$data->web,$data->registration,$act,$data->avatar,$data->invoice_address_id, $data->admin, $data->sex);
		return $object;
	}
}